﻿using System;
using System.Collections.Generic;
using System.Text;
using WMS.IDAL;
using WMS.DAL.DBUtility;
using System.Data;
using System.Data.SQLite;

namespace WMS.DAL.SQLite
{
    public class GoodsDB : IGoods
    {
        #region IGoods 成员

        private readonly string deleteSqlString = "delete from Goods where GoodsID=@goodsid";
        private readonly string insertSqlString = "insert into Goods (code,name,categoryid,Specification,unit,address,barcode,description,updatedate,createdate)"
                                                    + " values(@code,@name,@categoryid,@Specification,@unit,@address,@barcode,@description,@updatedate,@createdate)";
        private readonly string updateSqlString = "update Goods set code=@code,name=@name,categoryid=@categoryid,"
                                                    + " specification=@specification,unit=@unit,address=@address,"
                                                    + " barcode=@barcode,description=@description,updatedate=@updatedate,"
                                                    + " createdate=@createdate"
                                                    + " where goodsid=@goodsid";
        private readonly string maxIDSqlString = "select max(goodsid) from Goods";
        private readonly string selectSqlString = "select * from goods where goodsid=@goodsid";
        private readonly string sqlSelectByName = "select * from goods where name like '{0}%'";

        public int GetMaxID()
        {
            object obj = SQLiteHelper.ExecuteScalar(CommandType.Text, maxIDSqlString, null);

            return int.Parse(obj.ToString());
        }

        public bool Exists(int goodsID)
        {
            throw new NotImplementedException();
        }

        public int Add(WMS.Entity.GoodsEntity entity)
        {
            SQLiteParameter[] para = PreparePara(entity);
            int i = SQLiteHelper.ExecuteNonQuery(insertSqlString, para);
            entity.GoodsId = GetMaxID();
            return i;
        }

        public void Update(WMS.Entity.GoodsEntity entity)
        {
            SQLiteParameter[] para = PreparePara(entity);

            SQLiteHelper.ExecuteNonQuery(updateSqlString, para);
        }

        SQLiteParameter[] PreparePara(WMS.Entity.GoodsEntity entity)
        {
            #region
            SQLiteParameter[] para = new SQLiteParameter[]
            {
                new SQLiteParameter("@code",DbType.String),
                new SQLiteParameter("@name",DbType.String),
                new SQLiteParameter("@categoryid",DbType.Int32),
                new SQLiteParameter("@Specification",DbType.String),
                new SQLiteParameter("@unit",DbType.Int32),
                new SQLiteParameter("@address",DbType.String),
                new SQLiteParameter("@barcode",DbType.String),
                new SQLiteParameter("@description",DbType.String),
                new SQLiteParameter("@updatedate",DbType.DateTime),
                new SQLiteParameter("@createdate",DbType.DateTime),
                new SQLiteParameter("@goodsid",DbType.Int32)
            };

            para[0].Value = entity.Code;
            para[1].Value = entity.Name;
            para[2].Value = entity.CategoryId;
            para[3].Value = entity.Specification;
            para[4].Value = entity.Unit;
            para[5].Value = entity.Address;
            para[6].Value = entity.Barcode;
            para[7].Value = entity.Description;
            para[8].Value = entity.UpdateDate;
            para[9].Value = entity.CreateDate;
            para[10].Value = entity.GoodsId;
            return para;
            #endregion
        }

        public void Delete(int goodsID)
        {
            SQLiteParameter[] para = new SQLiteParameter[]
            {
                new SQLiteParameter("@goodsid",DbType.Int32)
            };
            para[0].Value = goodsID;

            SQLiteHelper.ExecuteNonQuery(deleteSqlString, para);
        }

        public WMS.Entity.GoodsEntity GetEntity(int goodsID)
        {
            #region
            SQLiteParameter[] para = new SQLiteParameter[]
            {
                new SQLiteParameter("@goodsid",DbType.Int32)
            };
            para[0].Value = goodsID;

            WMS.Entity.GoodsEntity entity = new WMS.Entity.GoodsEntity();
            using( SQLiteDataReader reader= SQLiteHelper.ExecuteReader(SQLiteHelper.ConnectionString,CommandType.Text, selectSqlString,para))
            {
                while (reader.Read())
                {
                    entity.GoodsId = int.Parse(reader["goodsid"].ToString());
                    entity.Code = reader["code"].ToString();
                    entity.Name = reader["name"].ToString();
                    entity.CategoryId = int.Parse(reader["categoryid"].ToString());
                    entity.Specification = reader["specification"].ToString();
                    entity.Unit = int.Parse(reader["unit"].ToString());
                    entity.Address = reader["address"].ToString();
                    entity.Barcode = reader["barcode"].ToString();
                    entity.Description = reader["description"].ToString();
                    entity.UpdateDate = DateTime.Parse(reader["updatedate"].ToString());
                    entity.CreateDate = DateTime.Parse(reader["createdate"].ToString());
                }

                if (reader.IsClosed == false)
                    reader.Close();
            }

            return entity;
            #endregion
        }

        public IList<WMS.Entity.GoodsEntity> GetListByName(string name)
        {
            #region
            IList<WMS.Entity.GoodsEntity> list = new List<WMS.Entity.GoodsEntity>();

            SQLiteParameter[] para = new SQLiteParameter[]
            {
                new SQLiteParameter("@name",DbType.String)
            };
            para[0].Value = name;
            name = name.Replace("'","\'");

            using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(SQLiteHelper.ConnectionString, CommandType.Text, string.Format(sqlSelectByName,name), para))
            {
                while (reader.Read())
                {
                    WMS.Entity.GoodsEntity entity = new WMS.Entity.GoodsEntity();
                    entity.GoodsId = int.Parse(reader["goodsid"].ToString());
                    entity.Code = reader["code"].ToString();
                    entity.Name = reader["name"].ToString();
                    entity.CategoryId = int.Parse(reader["categoryid"].ToString());
                    entity.Specification = reader["specification"].ToString();
                    entity.Unit = int.Parse(reader["unit"].ToString());
                    entity.Address = reader["address"].ToString();
                    entity.Barcode = reader["barcode"].ToString();
                    entity.Description = reader["description"].ToString();
                    entity.UpdateDate = DateTime.Parse(reader["updatedate"].ToString());
                    entity.CreateDate = DateTime.Parse(reader["createdate"].ToString());

                    list.Add(entity);
                }

                if (reader.IsClosed == false)
                    reader.Close();
            }

            return list;
            #endregion
        }

        public IList<WMS.Entity.GoodsEntity> GetList(string strWhere)
        {
            throw new NotImplementedException();
        }

        #endregion
    }
}
